<?php


/*
 * $_SERVER[MYSQL_HOME] = C:\xampp\mysql\bin
 */

namespace RedMVC\Db;

/**
 * MySql class
 *  
 * @category    RedMVC
 * @package     Db
 * @author      Jan Fischer, bitWorking <info@bitworking.de>
 */
class MySql extends ADb{
  
	public function connect($dbhost, $dbname, $dbusername, $dbpassword){			
		$this->dbh = @mysql_connect($dbhost, $dbusername, $dbpassword, true) or $this->printError();
        $this->setCharset();
        $this->setDb($dbname);
        $this->dbhost      = $dbhost;
        $this->dbname      = $dbname;
        $this->dbusername  = $dbusername;
        $this->dbpassword  = $dbpassword;
        return $this->dbh;
	}
	
	public function setDb($dbname){        
        @mysql_select_db($dbname, $this->dbh) or $this->printError();        
    }
    
    public function setCharset($charset = 'utf8'){        
        mysql_set_charset($charset, $this->dbh);
    }
	
	protected function printError(){
        throw new \Exception(mysql_error($this->dbh), mysql_errno($this->dbh));
	}
	
	public function replaceAndCleanArgs($query, array $args = array()){        
        if(count($args) === 0){
            return $query;
        }
        return vsprintf($query, array_map(array($this, 'realEscape'), $args));
	}
	
	protected function realEscape($value){
        if(null === $value){
            return 'NULL';
        }
        if(get_magic_quotes_gpc()){
            $value = stripslashes($value);
        }
        return mysql_real_escape_string($value, $this->dbh);
	}
	
	public function quote($value){
        if(null === $value){
            return 'NULL';
        }
        return "'".$this->realEscape($value)."'";
	}
	
	public function query($query, array $args = array()){
        if(count($args) > 0){
            $query  = str_replace(' ? ', " '%s' ", $query);
        }
        $query  = $this->replaceAndCleanArgs($query, $args);
        $this->_lastQuery = $query;
		$result = @mysql_query($query, $this->dbh);		
		if(!$result) {
			$this->printError();
		}
		return $result;
	}

	public function fetch($query, array $args = array()){
		$result   = $this->query($query, $args);
		$array    = Array();
        while($data = mysql_fetch_assoc($result)){
            $array[] = $data;
		}
		return $array;
	}
	
	public function fetchSelect($table, $valueField, $nameField, $selectSuffix = ''){
		$result = $this->query("
            SELECT 
            $valueField AS valueField, 
            $nameField AS nameField
            FROM ".$table." $selectSuffix");
		$array = Array();
		$array[''] = 'Bitte auswählen';
        while($data = mysql_fetch_assoc($result)){
            $array[$data['valueField']] = $data['nameField'];
		}
		return $array;
	}
	
	public function insert($table, array $valueArray, $onDuplicateKeyValues = null){
        $values = array_values($valueArray);        
        $values = array_map(array($this, 'realEscape'), $values);
        $values = array_map(array($this, '_wrapValue'), $values);
        $keys   = array_keys($valueArray);        
        $query  = "
            INSERT INTO `".$table."` (".implode(', ', $keys).") 
            VALUES (".implode(', ', $values).")
        ";
        $query .= $this->_onDuplicateKeyQuery($onDuplicateKeyValues);
        $result = $this->query($query);
        return $this->insertId();
	}

    protected function _wrapValue($value){
        if('NULL' == $value){
            return 'NULL';
        }
        return "'$value'";        
    }
		
	public function update($table, array $valueArray, $whereCondition = null){
        $query = "UPDATE `".$table."` SET ";
        // values
        $values = array();
        foreach($valueArray as $key=>$value){
            if(null === $value){
                $values[] = $key."=NULL";
            }
            else{
                $values[] = $key."=".$this->quote($value)."";
            }
        }
        $query .= implode(', ', $values);        
        $query .= $this->_whereCondition($whereCondition);
        $result = $this->query($query);
        return $this->affectedRows();
	}
	
	public function delete($table, $whereCondition = null){
        $query = "DELETE FROM `".$table."`";        
        $query .= $this->_whereCondition($whereCondition);
        $result = $this->query($query);
        return $this->affectedRows();
	}
	
	public function tableExists($table){
        $query  = "SELECT 1 FROM `".$table."` LIMIT 0";
        $result = @mysql_query($query, $this->dbh);
        if (!$result){
            return false;
        }
        return true;
    }	
	
	public function tableKeys($table){
		$result = $this->fetch("SELECT * FROM `".$table."` LIMIT 1");
        if(count($result)==0){return false;}
        return array_keys($result[0]);	
	}
	
	public function tableFields($table){
        $result     = $this->query("SELECT * FROM `".$table."` LIMIT 1");
        $i          = 0;
        $fields     = array();
        $numFields  = mysql_num_fields($result);
        while ($i < $numFields){
            $meta   = mysql_fetch_field($result, $i);
            $length = mysql_field_len($result, $i);
            $meta->length = $length;
            $fields[] = $meta;
            $i++;
        }
        return $fields;
	}
	
	public function count($query, array $args = array()){
		$query  = preg_replace('#SELECT\s.*?\sFROM#is', 'SELECT COUNT(*) FROM', $query, 1);
        $result = $this->query($query, $args);
        $row    = mysql_fetch_row($result);
		return $row[0];
	}
	
	public function insertId(){
	   return mysql_insert_id($this->dbh);
	}
	
	public function affectedRows(){
	    return mysql_affected_rows($this->dbh);
	}
	
	protected function numRows($result){
	    return mysql_num_rows($result);
	}
	
	public function beginTransaction(){
        $this->query("START TRANSACTION");
	}
	
    public function commit(){
        $this->query("COMMIT");
    }
    
    public function rollBack(){
        $this->query("ROLLBACK");
    }

	public function close(){
		mysql_close($this->dbh);
	}
	
	public function __deconstruct(){
		$this->close();
	}
		
}